package com.venutech.gongan.api;

import com.shihang.misc.date.DateTimeUtil;
import com.shihang.misc.excel.ExcelUtil;
import com.shihang.misc.file.HSSFExcelUtil;
import com.venutech.gongan.dao.QdsxjMapper;
import com.venutech.gongan.dto.rpc.common.dataimport.EquipAddressExportDto;
import com.venutech.gongan.dto.rpc.common.dataimport.EquipAddressExportDto02Axes;
import com.venutech.gongan.dto.rpc.common.dataimport.EquipImportDto;
import com.venutech.gongan.entity.DictData;
import com.venutech.gongan.entity.Jkgw;
import com.venutech.gongan.entity.Qdsxj;
import com.venutech.gongan.service.DictDataService;
import com.venutech.gongan.service.JkgwService;
import com.venutech.gongan.service.QdsxjService;
import com.venutech.gongan.util.Address;
import com.venutech.gongan.util.AddressUtils;
import com.venutech.gongan.util.GpsCoordinateUtils;
import com.venutech.gongan.vo.ApiResponse;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.commons.lang3.StringUtils;
import org.apache.logging.log4j.util.Strings;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import java.io.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

@Api(value = "设备信息数据导入的接口", tags = "设备信息")
@RestController
public class DataImportApiController {

    Logger logger = LoggerFactory.getLogger(DataImportApiController.class);

    private static final String AREA_CODE_GUSU = "320508";
    private static final String AREA_CODE_DICT_CODE = "xzqy";

    @Autowired
    QdsxjService qdsxjService;

    @Autowired
    DictDataService dictDataService;

    @Autowired
    @Qualifier("eventThreadPool")
    ExecutorService eventThreadPool;

    @Autowired
    QdsxjMapper qdsxjMapper;

    @Autowired
    JkgwService jkgwService;

    /**
     * 向数据库导入全量设备
     * @return
     */
    @ApiOperation("向数据库导入全量设备")
    @GetMapping("/api/external/dataImport/equipments")
    @ResponseBody
    public ApiResponse importEquipData() {

        eventThreadPool.execute(() -> {
            try {
                InputStream in = this.getClass().getResourceAsStream("/姑苏区前端摄像设备全量数据.xls");
                List<EquipImportDto> listToRead = HSSFExcelUtil.readData(in, EquipImportDto.class);
                qdsxjService.batchImport(listToRead);
            } catch(Exception ex) {
                logger.error("data import error: ", ex);
            }
        });
        return ApiResponse.success();
    }

    /**
     * 全量更新数据库中设备的矫正经纬度和精确地址
     * @return
     */
    @ApiOperation("全量更新数据库中设备的矫正经纬度和精确地址")
    @GetMapping("/api/external/dataImpoort/updateEquipmentsCorrectLocation")
    @ResponseBody
    public ApiResponse updateEquipments() {
        eventThreadPool.execute(() -> {
            try {
                List<Qdsxj> allEquipments = qdsxjService.getAll();
                logger.warn("updateEquipments: 读取全量监控设备，共{}个", allEquipments.size());
                if(CollectionUtils.isEmpty(allEquipments)) {
                    logger.warn("updateEquipments: 设备数量为0，退出矫正经纬度和参考街道地址");
                    return;
                }
                logger.warn("updateEquipments: 开始更新监控设备矫正经纬度, total count={}", allEquipments.size());
                AtomicInteger successNum = new AtomicInteger();
                allEquipments.forEach(equip -> {
                    if(equip.getSb_jd() >= 0 && equip.getSb_wd() >= 0 && Objects.isNull(equip.getCorrect_longitude()) && Objects.isNull(equip.getCorrect_latitude())) {
                        double correctLongitude = equip.getSb_jd();
                        double correctLatitude = equip.getSb_wd();
                        qdsxjService.updateCorrectLongitudeAndLatitude(equip.getSb_id(), correctLongitude, correctLatitude);
                        successNum.getAndIncrement();
                        logger.info("updateEquipments: [successNum={}][{}（经度={},纬度={}) {}] 矫正经纬度updated。", successNum, equip.getSb_id(), correctLongitude, correctLatitude, equip.getSb_name());
                        try {
                            Thread.sleep(50);
                        } catch (InterruptedException e) {
                            e.printStackTrace();
                        }
                    }
                });
                logger.warn("updateEquipments: 监控设备矫正经纬度全部更新完成。totalCount={}， successNum={}", allEquipments.size(), successNum.get());


                logger.warn("updateEquipments: 开始更新参考街道地址及街道编码, total count={}", allEquipments.size());
                AtomicInteger successNum2 = new AtomicInteger();
                allEquipments.forEach(equip -> {
                    if(equip.getSb_jd() > 0 && equip.getSb_wd() > 0 && StringUtils.isBlank(equip.getAccurate_address())) {
                        String accurateAddress = "";
                        String accurateTownCode = "";
                        String accurateTownName = "";
                        double[] wgs02 = GpsCoordinateUtils.calWGS84toGCJ02(equip.getSb_wd(), equip.getSb_jd());
                        if(Objects.nonNull(wgs02)) {
                            Address address = null;
                            try {
                                address = AddressUtils.getAddressByHttp(wgs02[1], wgs02[0]);
                            }catch(Exception ex) {
                                logger.error("updateEquipments: 获取参考街道异常，wgs02={}，  ex = ", wgs02, ex);
                            }
                            if(Objects.nonNull(address)) {
                                accurateAddress = address.getAddress();
                                if(StringUtils.isNotBlank(address.getStreetAndNumber())) {
                                    accurateAddress += "-" + address.getStreetAndNumber();
                                }
                                if(StringUtils.isNotBlank(address.getBuilding())) {
                                    accurateAddress += "-" + address.getBuilding();
                                }
                                if(StringUtils.isNotBlank(address.getTownCode())) {
                                    accurateTownCode = address.getTownCode();
                                }
                                if(StringUtils.isNotBlank(address.getTownName())) {
                                    accurateTownName = address.getTownName();
                                }
                            }
                        }

                        if(!StringUtils.isBlank((accurateAddress))){
                            qdsxjService.updateAccurateAddress(equip.getSb_id(), accurateAddress, accurateTownCode, accurateTownName);
                            successNum2.getAndIncrement();
                            logger.info("updateEquipments: [successNum={}][{}（经度={},纬度={}) {}] 参考街道地址updated。参考街道地址={}， 参考街道编码={}， 街道名称={}",
                                    successNum2, equip.getSb_id(), equip.getSb_jd(), equip.getSb_wd(), equip.getSb_name(), accurateAddress, accurateTownCode, accurateTownName);
                            try {
                                Thread.sleep(50);
                            } catch (InterruptedException e) {
                                e.printStackTrace();
                            }
                        }
                    }

                });
                logger.warn("updateEquipments: 参考街道地址全部更新完成。totalCount={}， successNum={}", allEquipments.size(), successNum2.get());


            } catch(Exception ex) {
                logger.error("data import error: ", ex);
            }
        });

        return ApiResponse.success();
    }

    @GetMapping("/api/external/dataImpoort/updateEquipmentsMonitorType")
    @ResponseBody
    public ApiResponse updateMonitorType() {
        eventThreadPool.execute(() -> {
            try {
                InputStream in = this.getClass().getResourceAsStream("/姑苏区前端摄像设备全量数据.xls");
                List<EquipImportDto> listToRead = HSSFExcelUtil.readData(in, EquipImportDto.class);
                qdsxjService.batchUpdateMonitorType(listToRead);

            } catch (Exception ex) {
                logger.error("update monitor error: ", ex);
            }
        });
        return ApiResponse.success();
    }


    /**
     * 细分行政区域导出设备地址信息到ｅｘｃｅｌ文件
     * @return
     */
//    @GetMapping("/api/external/dataImpoort/exportToFileForAreas")
//    @ResponseBody
//    public ApiResponse exportEquipmentsInfoToFile() {
//        eventThreadPool.execute(() -> {
//            try {
//
////                double xx = qdsxjMapper.selectGeometry();
////                logger.info("selectGeometry: {}", xx);
////                double radius = 0.02;
////                List<Qdsxj> lst = qdsxjMapper.getCameraInEquipmenteList2(120.627111d,31.295963d,radius);
////                logger.info("getCameraInEquipmenteList2: size={}", lst.size());
//
//                List<String> subAreaCodeList = qdsxjService.getSubAreaCodeList(AREA_CODE_GUSU);
//                if(CollectionUtils.isEmpty(subAreaCodeList)) {
//                    logger.warn("exportEquipmentsInfoToFile: 片区行政区域列表为空，导出终止");
//                    return;
//                }
//
//                subAreaCodeList.forEach(subAreaCode -> {
//                    DictData dictData = dictDataService.getDictDataByDictValueAndDictType(subAreaCode, AREA_CODE_DICT_CODE);
//                    String subAreaName = Objects.nonNull(dictData) ? dictData.getDict_label():null;
//                    if(Objects.isNull(subAreaName)) {
//                        subAreaName = "";
//                    }
//
//                    List<String> titles = new ArrayList<>();
//                    titles.add("设备ID");
//                    titles.add("点位名称");
//                    titles.add("门楼详址");
//                    titles.add("参考地址");
//                    titles.add("经度");
//                    titles.add("纬度");
//                    titles.add("行政区域");
//
//
//                    List<EquipAddressExportDto> exportDtos = qdsxjService.getEquipmentListBySubAreaCode(subAreaCode, subAreaName);
//                    String fileName = "";
//                    if(Strings.isBlank(subAreaName)) {
//                        fileName = String.format("%s-%s.xls", subAreaCode, DateTimeUtil.formatDate(new Date(), "yyyyMMddHHmmss"));
//                    } else {
//                        fileName = String.format("%s%s-%s.xls", subAreaCode, subAreaName, DateTimeUtil.formatDate(new Date(), "yyyyMMddHHmmss"));
//                    }
//
//                    writeToFile(titles, exportDtos, fileName);
//
//                });
//            } catch (Exception ex) {
//                logger.error("data export error: ", ex);
//            }
//        });
//
//        return ApiResponse.success();
//    }

    /**
     * 根据街道区域导出设备地址信息到ｅｘｃｅｌ文件
     * @return
     */
    @ApiOperation("根据街道区域导出设备地址信息到ｅｘｃｅｌ文件")
    @GetMapping("/api/external/dataImpoort/exportToFileForTown")
    @ResponseBody
    public ApiResponse exportEquipmentsInfoToFile2() {
        eventThreadPool.execute(() -> {
            try {

                List<String> townCodeList = qdsxjService.getTownCodeList(AREA_CODE_GUSU);
                if(CollectionUtils.isEmpty(townCodeList)) {
                    logger.warn("exportEquipmentsInfoToFile2: 街道行政区域列表为空，导出终止");
                    return;
                }

                townCodeList.forEach(townCode -> {
                    List<String> titles = new ArrayList<>();
                    titles.add("设备ID");
                    titles.add("点位名称");
                    titles.add("门楼详址");
                    titles.add("参考地址");
                    titles.add("经度");
                    titles.add("纬度");
                    titles.add("街道行政区域");

                    List<EquipAddressExportDto> exportDtos = qdsxjService.getEquipmentListByTownCode(townCode);
                    if(Strings.isBlank(townCode)) {    // 没有细分街道，再按照行政区域(sb_xzqy)分查
                        List<String> subAreaCodeList = exportDtos.stream().map(dto -> dto.getSubAreaCode()).distinct().collect(Collectors.toList());
                        subAreaCodeList.forEach(subAreaCode -> {
                            DictData dictData = dictDataService.getDictDataByDictValueAndDictType(subAreaCode, AREA_CODE_DICT_CODE);
                            final String subAreaName = Objects.nonNull(dictData) ? dictData.getDict_label():"";
                            List<EquipAddressExportDto> subExportDtos = exportDtos.stream().filter(dto -> dto.getSubAreaCode().equals(subAreaCode)).collect(Collectors.toList());
                            subExportDtos.forEach(address -> {
                                address.setTownArea(subAreaCode+subAreaName);
                            });
                            final String fileNm = String.format("%s-%s.xls", subAreaCode+subAreaName, DateTimeUtil.formatDate(new Date(), "yyyyMMddHHmmss"));
                            writeToFile(titles, subExportDtos, fileNm);
                        });
                    } else {          // 数据大于100行时，分下文件
                        int pageSize = 100;
                        if(exportDtos.size() > pageSize) {
                            int page = exportDtos.size()/pageSize;
                            int yushu = exportDtos.size()%pageSize;
                            int splitCount = page;
                            if(yushu > 50) {
                                splitCount = page +1;
                            }
                            for(int pageIndex = 0; pageIndex < splitCount; pageIndex ++) {
                                int startIndex = pageIndex*pageSize;
                                int endIndex = 0;
                                if(pageIndex == splitCount -1) {
                                    endIndex = exportDtos.size() - 1;
                                } else {
                                    endIndex = (pageIndex+1)*pageSize;
                                }
                                List<EquipAddressExportDto> subExportDtos = exportDtos.subList(startIndex, endIndex);
                                String fileName = String.format("%s-%d-%s.xls", exportDtos.get(0).getTownArea(), pageIndex+1, DateTimeUtil.formatDate(new Date(), "yyyyMMddHHmmss"));
                                writeToFile(titles, subExportDtos, fileName);
                            }

                        } else {
                            String fileName = String.format("%s-%s.xls", exportDtos.get(0).getTownArea(), DateTimeUtil.formatDate(new Date(), "yyyyMMddHHmmss"));
                            writeToFile(titles, exportDtos, fileName);
                        }
                    }
                });
            } catch (Exception ex) {
                logger.error("data export error: ", ex);
            }
        });

        return ApiResponse.success();
    }

    /**
     * 根据街道区域导出设备地址信息到ｅｘｃｅｌ文件, 经纬度从84转换到02坐标系
     * @return
     */
    @ApiOperation("根据街道区域导出设备地址信息到ｅｘｃｅｌ文件, 经纬度从84转换到02坐标系")
    @GetMapping("/api/external/dataImpoort/exportToFileForTownWith02Axes")
    @ResponseBody
    public ApiResponse exportEquipmentsInfoToFileWith02Axes() {
        eventThreadPool.execute(() -> {
            try {

                List<String> townCodeList = qdsxjService.getTownCodeList(AREA_CODE_GUSU);
                if(CollectionUtils.isEmpty(townCodeList)) {
                    logger.warn("exportEquipmentsInfoToFileWith02Axes: 街道行政区域列表为空，导出终止");
                    return;
                }

                townCodeList.forEach(townCode -> {
                    List<String> titles = new ArrayList<>();
                    titles.add("标记名称");
                    titles.add("详细地址");
                    titles.add("经度");
                    titles.add("纬度");

                    List<EquipAddressExportDto02Axes> exportDtos = qdsxjService.getEquipmentListByTownCode2(townCode);
                    if(Strings.isBlank(townCode)) {    // 没有细分街道，再按照行政区域(sb_xzqy)分查
                        List<String> subAreaCodeList = exportDtos.stream().map(dto -> dto.getSubAreaCode()).distinct().collect(Collectors.toList());
                        subAreaCodeList.forEach(subAreaCode -> {
                            DictData dictData = dictDataService.getDictDataByDictValueAndDictType(subAreaCode, AREA_CODE_DICT_CODE);
                            final String subAreaName = Objects.nonNull(dictData) ? dictData.getDict_label():"";
                            List<EquipAddressExportDto02Axes> subExportDtos = exportDtos.stream().filter(dto -> dto.getSubAreaCode().equals(subAreaCode)).collect(Collectors.toList());
                            subExportDtos.forEach(address -> {
                                address.setTownArea(subAreaCode+subAreaName);
                            });
                            final String fileNm = String.format("[02Axes]%s-%s.xls", subAreaCode+subAreaName, DateTimeUtil.formatDate(new Date(), "yyyyMMddHHmmss"));
                            writeToFile2(titles, subExportDtos, fileNm);
                        });
                    } else {          // 数据大于100行时，分下文件
                        int pageSize = 100;
                        if(exportDtos.size() > pageSize) {
                            int page = exportDtos.size()/pageSize;
                            int yushu = exportDtos.size()%pageSize;
                            int splitCount = page;
                            if(yushu > 50) {
                                splitCount = page +1;
                            }
                            for(int pageIndex = 0; pageIndex < splitCount; pageIndex ++) {
                                int startIndex = pageIndex*pageSize;
                                int endIndex = 0;
                                if(pageIndex == splitCount -1) {
                                    endIndex = exportDtos.size() - 1;
                                } else {
                                    endIndex = (pageIndex+1)*pageSize;
                                }
                                List<EquipAddressExportDto02Axes> subExportDtos = exportDtos.subList(startIndex, endIndex);
                                String fileName = String.format("[02Axes]%s-%d-%s.xls", exportDtos.get(0).getTownArea(), pageIndex+1, DateTimeUtil.formatDate(new Date(), "yyyyMMddHHmmss"));
                                writeToFile2(titles, subExportDtos, fileName);
                            }

                        } else {
                            String fileName = String.format("[02Axes]%s-%s.xls", exportDtos.get(0).getTownArea(), DateTimeUtil.formatDate(new Date(), "yyyyMMddHHmmss"));
                            writeToFile2(titles, exportDtos, fileName);
                        }
                    }
                });
            } catch (Exception ex) {
                logger.error("data export error: ", ex);
            }
        });

        return ApiResponse.success();
    }

    /**
     * 补全已录入杆位的街道名称和街道行政编码
     * @return
     */
    @ApiOperation("补全已录入杆位的街道名称和街道行政编码")
    @GetMapping("/api/external/dataImport/updatePolesAreaCodeAndTownCode")
    @ResponseBody
    public ApiResponse updatePolesAreaCodeAndTownCode() {
        eventThreadPool.execute(() -> {
            try {
                List<Jkgw> allPoles =  jkgwService.getAll();
                logger.warn("updatePolesAreaCodeAndTownCode: 读取全量杆位，共{}个", allPoles.size());
                if(CollectionUtils.isEmpty(allPoles)) {
                    logger.warn("updatePolesAreaCodeAndTownCode: 杆位数量为0，退出");
                    return;
                }
                logger.warn("updatePolesAreaCodeAndTownCode: 开始补全已录入杆位的行政区域和街道, total count={}", allPoles.size());
                AtomicInteger successNum = new AtomicInteger();
                allPoles.forEach(pole -> {
                    if(Objects.nonNull(pole.getGw_jd()) && pole.getGw_jd().doubleValue() >0 && Objects.nonNull(pole.getGw_wd()) && pole.getGw_wd().doubleValue() >0) {
                        if(Strings.isBlank(pole.getArea_code()) || Strings.isBlank(pole.getArea_name()) || Strings.isBlank(pole.getTown_code()) || Strings.isBlank(pole.getTown_name())) {
                            Address address = getGaodeAddress(pole.getGw_jd(), pole.getGw_wd());
                            if(Objects.nonNull(address) && Strings.isNotBlank(address.getAreaName()) && Strings.isNotBlank(address.getAreaCode()) && Strings.isNotBlank(address.getTownName()) && Strings.isNotBlank(address.getTownCode()) ) {
                                jkgwService.updateAreaCodeAndTownCode(pole.getGw_id(), address.getAreaCode(), address.getAreaName(), address.getTownCode(), address.getTownName());
                                successNum.getAndIncrement();
                                logger.info("updatePolesAreaCodeAndTownCode: [successNum={}] 杆件行政区域和街道 updated。poleId={}, 杆位名称={}, 行政区域={}，行政编码={}，街道名称={}， 街道编码={}",
                                        successNum, pole.getGw_id(), pole.getGw_name(), address.getAreaName(), address.getAreaCode(), address.getTownName(), address.getTownCode());
                            }
                        }
                    }
                });
                logger.warn("updatePolesAreaCodeAndTownCode: 所有杆件行政区域和街道更新完成。totalCount={}， successNum={}", allPoles.size(), successNum.get());

            } catch (Exception ex) {
                logger.error("updatePolesAreaCodeAndTownCode error: ", ex);
            }
        });
        return ApiResponse.success();
    }


    /**
     * 增补导入设备数据（2021.11）
     * @return
     */
    @ApiOperation("增补导入设备数据")
    @GetMapping("/api/external/dataImport/supply/equipments")
    @ResponseBody
    public ApiResponse supplyEquipData() {
        eventThreadPool.execute(() -> {
            try {
                InputStream in = this.getClass().getResourceAsStream("/姑苏区前端摄像设备全量数据1125(根据所属公安机关查询).xls");
                List<EquipImportDto> listToRead = HSSFExcelUtil.readData(in, EquipImportDto.class);
                qdsxjService.batchSupplyImport(listToRead);

            } catch (Exception ex) {
                logger.error("data supply import error: ", ex);
            }
        });
        return ApiResponse.success();
    }

    private void writeToFile(List<String> titles, List<EquipAddressExportDto> exportDtos, String fileName) {
        File file = new File(fileName);
        try {
            OutputStream fos = new FileOutputStream(file);
            String[] headers = new String[titles.size()];
            titles.toArray(headers);
            ExcelUtil.exportExcel(headers, exportDtos, fos);
            fos.flush();
            fos.close();

        } catch (IOException e) {
            logger.error("exportEquipmentsInfoToFile: 导出文件失败，", e);
        }
    }

    private void writeToFile2(List<String> titles, List<EquipAddressExportDto02Axes> exportDtos, String fileName) {
        File file = new File(fileName);
        try {
            OutputStream fos = new FileOutputStream(file);
            String[] headers = new String[titles.size()];
            titles.toArray(headers);
            ExcelUtil.exportExcel(headers, exportDtos, fos);
            fos.flush();
            fos.close();

        } catch (IOException e) {
            logger.error("exportEquipmentsInfoToFile: 导出文件失败，", e);
        }
    }

    private Address getGaodeAddress(double longitude, double latitude) {
        double[] wgs02 = GpsCoordinateUtils.calWGS84toGCJ02(latitude, longitude);
        Address address = null;
        if(Objects.nonNull(wgs02)) {
            try {
                address = AddressUtils.getAddressByHttp(wgs02[1], wgs02[0]);
            }catch(Exception ex) {
                logger.error("getGaodeAddress: 根据经纬度获取地址信息异常，wgs02={}，  ex = ", wgs02, ex);
            }
        }
        return address;
    }

}
